package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 查看某部门名称下工资高于某值的所有员工信息
 * 用户可以输入部门名称和工资
 *
 * 例如:
 * 神仙 2000
 * 查看部门名称为'神仙'工资>2000的
 *
 */
public class Test6 {
    public static void main(String[] args) {
        try(
                Connection connection = DBUtil.getConnection();
        ){
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入部门名称:");
            String dname = scanner.nextLine();
            System.out.println("请输入工资:");
            int sal = scanner.nextInt();
            /*
                SELECT e.name,e.sal,d.name
                FROM emp e
                JOIN dept d
                ON e.dept_id=d.id
                WHERE d.name=?
                AND e.sal>?
             */
            String sql = "SELECT e.name,e.sal,d.name " +
                         "FROM emp e " +
                         "JOIN dept d " +
                         "ON e.dept_id=d.id " +
                         "WHERE d.name=? " +
                         "AND e.sal>?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,dname);
            ps.setInt(2,sal);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                String ename = rs.getString(1);
                int salary = rs.getInt(2);
                String deptName = rs.getString(3);
                System.out.println(ename+","+salary+","+deptName);
            }
            rs.close();


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}








